/* SQL script to initialize of database for Resolver in version TODO */
  
/* Drop Indexes */

DROP INDEX IF EXISTS DIGITALDOCUMENT_ENTITYID;
DROP INDEX IF EXISTS DIGITALINSTANCE_DIGDOCID;
DROP INDEX IF EXISTS LIBRARY_REGISTRARID;
DROP INDEX IF EXISTS IEIDENTIFIER_VALUE;
DROP INDEX IF EXISTS URNNBN_REGISTRARCODEDOCUMENTCODE;
DROP INDEX IF EXISTS URNNBNRESERVED_REGISTRARCODEDOCUMENTCODE;
DROP INDEX IF EXISTS URNNBN_PREDECESSOR;
DROP INDEX IF EXISTS URNNBN_SUCCESSOR;
DROP INDEX IF EXISTS URNNBN_PREDECESSOR_SUCCESSOR;



/* Drop Tables */

DROP TABLE IF EXISTS DIGITALINSTANCE;
DROP TABLE IF EXISTS REGISTRARSCOPEID;
DROP TABLE IF EXISTS URNNBN;
DROP TABLE IF EXISTS DIGITALDOCUMENT;
DROP TABLE IF EXISTS CATALOGUE;
DROP TABLE IF EXISTS DIGITALLIBRARY;
DROP TABLE IF EXISTS URNNBNGENERATOR;
DROP TABLE IF EXISTS URNNBNRESERVED;
DROP TABLE IF EXISTS USER_REGISTRAR;
DROP TABLE IF EXISTS REGISTRAR;
DROP TABLE IF EXISTS ARCHIVER;
DROP TABLE IF EXISTS IEIDENTIFIER;
DROP TABLE IF EXISTS ORIGINATOR;
DROP TABLE IF EXISTS PUBLICATION;
DROP TABLE IF EXISTS SOURCEDOCUMENT;
DROP TABLE IF EXISTS INTELECTUALENTITY;
DROP TABLE IF EXISTS URNNBNSUCCESSORS;
DROP TABLE IF EXISTS USERACCOUNT;
DROP TABLE IF EXISTS CONTENT;
DROP TABLE IF EXISTS IE_TITLE;


/* Drop Sequences */

DROP SEQUENCE IF EXISTS SEQ_ARCHIVER;
DROP SEQUENCE IF EXISTS SEQ_CATALOGUE;
DROP SEQUENCE IF EXISTS SEQ_DIGITALDOCUMENT;
DROP SEQUENCE IF EXISTS SEQ_DIGITALINSTANCE;
DROP SEQUENCE IF EXISTS SEQ_DIGITALLIBRARY;
DROP SEQUENCE IF EXISTS SEQ_INTELECTUALENTITY;
DROP SEQUENCE IF EXISTS SEQ_USERACCOUNT;
DROP SEQUENCE IF EXISTS SEQ_CONTENT;




/* Create Sequences */

CREATE SEQUENCE SEQ_ARCHIVER;
CREATE SEQUENCE SEQ_CATALOGUE;
CREATE SEQUENCE SEQ_DIGITALDOCUMENT;
CREATE SEQUENCE SEQ_DIGITALINSTANCE;
CREATE SEQUENCE SEQ_DIGITALLIBRARY;
CREATE SEQUENCE SEQ_INTELECTUALENTITY;
CREATE SEQUENCE SEQ_USERACCOUNT;
CREATE SEQUENCE SEQ_CONTENT;



/* Create Tables */

CREATE TABLE ARCHIVER
(
	ID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	NAME VARCHAR NOT NULL,
	DESCRIPTION VARCHAR,
    ITEM_ORDER NUMERIC DEFAULT 0,
    HIDDEN BOOLEAN DEFAULT false,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE CATALOGUE
(
	ID NUMERIC NOT NULL,
	REGISTRARID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	NAME VARCHAR NOT NULL,
	DESCRIPTION VARCHAR,
	URLPREFIX VARCHAR NOT NULL,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE DIGITALDOCUMENT
(
	ID NUMERIC NOT NULL,
	INTELECTUALENTITYID NUMERIC NOT NULL,
	REGISTRARID NUMERIC NOT NULL,
	ARCHIVERID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	FINANCEDFROM VARCHAR,
	CONTRACTNUMBER VARCHAR,
	FORMAT VARCHAR,
	FORMATVERSION VARCHAR,
	EXTENT VARCHAR,
	RESOLUTIONHORIZONTAL INT,
	RESOLUTIONVERTICAL INT,
	COMPRESSION VARCHAR,
	COMPRESSIONRATIO FLOAT,
	COLORMODEL VARCHAR,
	COLORDEPTH INT,
	ICCPROFILE VARCHAR,
	PICTUREWIDTH INT,
	PICTUREHEIGHT INT,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE DIGITALINSTANCE
(
	ID NUMERIC NOT NULL,
	DIGITALDOCUMENTID NUMERIC NOT NULL,
	DIGITALLIBRARYID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	DEACTIVATED TIMESTAMP,
	ACTIVE BOOLEAN NOT NULL,
	URL VARCHAR NOT NULL,
	FORMAT VARCHAR,
	ACCESSIBILITY VARCHAR,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE DIGITALLIBRARY
(
	ID NUMERIC NOT NULL,
	REGISTRARID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	NAME VARCHAR NOT NULL,
	DESCRIPTION VARCHAR,
	URL VARCHAR,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE IEIDENTIFIER
(
	INTELECTUALENTITYID NUMERIC NOT NULL,
	TYPE VARCHAR NOT NULL,
	IDVALUE VARCHAR NOT NULL,
	PRIMARY KEY (INTELECTUALENTITYID, TYPE)
) WITHOUT OIDS;


CREATE TABLE INTELECTUALENTITY
(
	ID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	ENTITYTYPE VARCHAR NOT NULL,
	DOCUMENTTYPE VARCHAR,
	DIGITALBORN BOOLEAN NOT NULL,
	OTHERORIGINATOR VARCHAR,
	DEGREEAWARDINGINSTITUTION VARCHAR,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE ORIGINATOR
(
	INTELECTUALENTITYID NUMERIC NOT NULL,
	ORIGINTYPE VARCHAR NOT NULL,
	ORIGINVALUE VARCHAR NOT NULL,
	PRIMARY KEY (INTELECTUALENTITYID)
) WITHOUT OIDS;


CREATE TABLE PUBLICATION
(
	INTELECTUALENTITYID NUMERIC NOT NULL,
	PYEAR DECIMAL(4),
	PLACE VARCHAR,
	PUBLISHER VARCHAR,
	PRIMARY KEY (INTELECTUALENTITYID)
) WITHOUT OIDS;


CREATE TABLE REGISTRAR
(
	ID NUMERIC NOT NULL,
	CODE VARCHAR NOT NULL UNIQUE,
	ALLOWEDREGISTRATIONMODEBYREGISTRAR BOOLEAN NOT NULL,
	ALLOWEDREGISTRATIONMODEBYRESOLVER BOOLEAN NOT NULL,
	ALLOWEDREGISTRATIONMODEBYRESERVATION BOOLEAN NOT NULL,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE REGISTRARSCOPEID
(
	REGISTRARID NUMERIC NOT NULL,
	DIGITALDOCUMENTID NUMERIC NOT NULL,
	TYPE VARCHAR NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	IDVALUE VARCHAR NOT NULL,
	PRIMARY KEY (REGISTRARID, DIGITALDOCUMENTID, TYPE),
	CONSTRAINT registrar_idType_idValue UNIQUE (REGISTRARID, TYPE, IDVALUE)
) WITHOUT OIDS;


CREATE TABLE SOURCEDOCUMENT
(
	INTELECTUALENTITYID NUMERIC NOT NULL,
	TITLE VARCHAR,
	VOLUMETITLE VARCHAR,
	ISSUETITLE VARCHAR,
	CCNB VARCHAR,
	ISBN VARCHAR,
	ISSN VARCHAR,
	OTHERID VARCHAR,
	PUBLISHER VARCHAR,
	PUBLICATIONPLACE VARCHAR,
	PUBLICATIONYEAR NUMERIC(4),
	PRIMARY KEY (INTELECTUALENTITYID)
) WITHOUT OIDS;


CREATE TABLE URNNBN
(
	DIGITALDOCUMENTID NUMERIC NOT NULL,
	RESERVED TIMESTAMP,
	REGISTERED TIMESTAMP NOT NULL,
	DEACTIVATED TIMESTAMP,
	REGISTRARCODE VARCHAR NOT NULL,
	DOCUMENTCODE VARCHAR NOT NULL,
	ACTIVE BOOLEAN NOT NULL,
    DEACTIVATIONNOTE VARCHAR,
	PRIMARY KEY (DIGITALDOCUMENTID),
	CONSTRAINT registrarCodeDocumentCode UNIQUE (REGISTRARCODE, DOCUMENTCODE)
) WITHOUT OIDS;


CREATE TABLE URNNBNGENERATOR
(
	REGISTRARID NUMERIC NOT NULL,
	LASTDOCUMENTCODE VARCHAR DEFAULT '000000' NOT NULL,
	PRIMARY KEY (REGISTRARID)
) WITHOUT OIDS;


CREATE TABLE URNNBNRESERVED
(
	REGISTRARID NUMERIC NOT NULL,
	DOCUMENTCODE VARCHAR NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	REGISTRARCODE VARCHAR NOT NULL,
	PRIMARY KEY (REGISTRARID, DOCUMENTCODE)
) WITHOUT OIDS;


CREATE TABLE URNNBNSUCCESSORS
(
	PREDECESSORREGCODE VARCHAR NOT NULL,
	PREDECESSORDOCCODE VARCHAR NOT NULL,
	SUCCESSORREGCODE VARCHAR NOT NULL,
	SUCCESSORDOCCODE VARCHAR NOT NULL,
	NOTE VARCHAR
) WITHOUT OIDS;


CREATE TABLE USERACCOUNT
(
	ID NUMERIC NOT NULL,
	CREATED TIMESTAMP NOT NULL,
	MODIFIED TIMESTAMP NOT NULL,
	LOGIN VARCHAR NOT NULL UNIQUE,
	PASSHASH VARCHAR NOT NULL,
	PASSSALT VARCHAR NOT NULL,
	ISADMIN BOOLEAN DEFAULT 'false' NOT NULL,
	EMAIL VARCHAR NOT NULL,
	PRIMARY KEY (ID)
) WITHOUT OIDS;


CREATE TABLE USER_REGISTRAR
(
	REGISTRARID NUMERIC NOT NULL,
	USERACCOUNTID NUMERIC NOT NULL,
	PRIMARY KEY (REGISTRARID, USERACCOUNTID)
) WITHOUT OIDS;

CREATE TABLE CONTENT (
	ID NUMERIC NOT NULL,
	LANGUAGE VARCHAR NOT NULL,
    NAME VARCHAR NOT NULL,
    CONTENT VARCHAR NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT languageName UNIQUE (LANGUAGE, NAME)
) WITHOUT OIDS;


CREATE TABLE IE_TITLE (
   ID NUMERIC NOT NULL,
   TITLE VARCHAR
) WITHOUT OIDS;

/* Create Foreign Keys */

ALTER TABLE DIGITALDOCUMENT
	ADD FOREIGN KEY (ARCHIVERID)
	REFERENCES ARCHIVER (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE REGISTRAR
	ADD FOREIGN KEY (ID)
	REFERENCES ARCHIVER (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE DIGITALINSTANCE
	ADD FOREIGN KEY (DIGITALDOCUMENTID)
	REFERENCES DIGITALDOCUMENT (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE REGISTRARSCOPEID
	ADD FOREIGN KEY (DIGITALDOCUMENTID)
	REFERENCES DIGITALDOCUMENT (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE URNNBN
	ADD FOREIGN KEY (DIGITALDOCUMENTID)
	REFERENCES DIGITALDOCUMENT (ID)
	ON UPDATE RESTRICT
	ON DELETE SET NULL
;


ALTER TABLE DIGITALINSTANCE
	ADD FOREIGN KEY (DIGITALLIBRARYID)
	REFERENCES DIGITALLIBRARY (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE DIGITALDOCUMENT
	ADD FOREIGN KEY (INTELECTUALENTITYID)
	REFERENCES INTELECTUALENTITY (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE IEIDENTIFIER
	ADD FOREIGN KEY (INTELECTUALENTITYID)
	REFERENCES INTELECTUALENTITY (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE ORIGINATOR
	ADD FOREIGN KEY (INTELECTUALENTITYID)
	REFERENCES INTELECTUALENTITY (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE PUBLICATION
	ADD FOREIGN KEY (INTELECTUALENTITYID)
	REFERENCES INTELECTUALENTITY (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE SOURCEDOCUMENT
	ADD FOREIGN KEY (INTELECTUALENTITYID)
	REFERENCES INTELECTUALENTITY (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE CATALOGUE
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE DIGITALDOCUMENT
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE DIGITALLIBRARY
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE REGISTRARSCOPEID
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
;


ALTER TABLE URNNBNGENERATOR
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE URNNBNRESERVED
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE USER_REGISTRAR
	ADD FOREIGN KEY (REGISTRARID)
	REFERENCES REGISTRAR (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;


ALTER TABLE USER_REGISTRAR
	ADD FOREIGN KEY (USERACCOUNTID)
	REFERENCES USERACCOUNT (ID)
	ON UPDATE RESTRICT
	ON DELETE CASCADE
;



/* Create Indexes */

CREATE INDEX DIGITALDOCUMENT_ENTITYID ON DIGITALDOCUMENT (INTELECTUALENTITYID);
CREATE INDEX DIGITALINSTANCE_DIGDOCID ON DIGITALINSTANCE (DIGITALDOCUMENTID);
CREATE INDEX LIBRARY_REGISTRARID ON DIGITALLIBRARY (REGISTRARID);
CREATE INDEX IEIDENTIFIER_VALUE ON IEIDENTIFIER (IDVALUE);
CREATE INDEX REGISTRARSCOPEID_REGISTRARID_TYPE_VALUE ON REGISTRARSCOPEID (REGISTRARID, TYPE, IDVALUE);
CREATE INDEX URNNBN_REGISTRARCODEDOCUMENTCODE ON URNNBN (REGISTRARCODE, DOCUMENTCODE);
CREATE INDEX URNNBNRESERVED_REGISTRARCODEDOCUMENTCODE ON URNNBNRESERVED (REGISTRARCODE, DOCUMENTCODE);
CREATE INDEX URNNBN_PREDECESSOR ON URNNBNSUCCESSORS (PREDECESSORREGCODE, PREDECESSORDOCCODE);
CREATE INDEX URNNBN_SUCCESSOR ON URNNBNSUCCESSORS (SUCCESSORREGCODE, SUCCESSORDOCCODE);
CREATE INDEX URNNBN_PREDECESSOR_SUCCESSOR ON URNNBNSUCCESSORS (PREDECESSORREGCODE, PREDECESSORDOCCODE, SUCCESSORREGCODE, SUCCESSORDOCCODE);
CREATE INDEX ie_title_fulltext_idx ON ie_title USING gin(to_tsvector('simple', lower(title)));


/* Create initial administrator account */
INSERT INTO USERACCOUNT(ID, CREATED, MODIFIED, LOGIN, PASSWORD, ISADMIN, EMAIL) 
VALUES(nextval('SEQ_USERACCOUNT'), LOCALTIMESTAMP, LOCALTIMESTAMP, 'admin', 'adminPassword', true, 'somone@somewhere.com');

/* Initialize content of panels "info" and "rules" for language version "cs", "en" */
INSERT INTO CONTENT(ID, LANGUAGE, NAME, CONTENT) VALUES (nextval('SEQ_CONTENT'), 'cs', 'info', '');
INSERT INTO CONTENT(ID, LANGUAGE, NAME, CONTENT) VALUES (nextval('SEQ_CONTENT'), 'en', 'info', '');
INSERT INTO CONTENT(ID, LANGUAGE, NAME, CONTENT) VALUES (nextval('SEQ_CONTENT'), 'cs', 'rules', '');
INSERT INTO CONTENT(ID, LANGUAGE, NAME, CONTENT) VALUES (nextval('SEQ_CONTENT'), 'en', 'rules', '');


/* REMAINING IE-TITLE-SEARCH STUFF */
CREATE OR REPLACE VIEW IE_TITLE_VIEW AS
SELECT
   title.id AS id,
   COALESCE(title.value, '') ||
   case when subtitle.value is null then ''
        else ' ' || subtitle.value
   end ||
   case when volume.value is null then ''
        else ' ' || volume.value
   end ||
   case when issue.value is null then ''
        else ' ' || issue.value
   end ||
   case when ccnb.value is null then ''
        else ' ' || ccnb.value
   end ||
   case when isbn.value is null then ''
        else ' ' || isbn.value
   end ||
   case when issn.value is null then ''
        else ' ' || issn.value
   end
as title
FROM
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'TITLE') AS title FULL OUTER JOIN
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'SUB_TITLE') AS subtitle ON title.id = subtitle.id FULL OUTER JOIN
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'VOLUME_TITLE') AS volume ON title.id = volume.id FULL OUTER JOIN
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'ISSUE_TITLE') AS issue ON title.id = issue.id FULL OUTER JOIN
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'CCNB') AS ccnb ON title.id = ccnb.id FULL OUTER JOIN
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'ISBN') AS isbn ON title.id = isbn.id FULL OUTER JOIN
   (SELECT intelectualentityid AS id, idvalue AS value FROM ieidentifier WHERE type = 'ISSN') AS issn ON title.id = issn.id
;
 
CREATE LANGUAGE plpgsql;
 
CREATE OR REPLACE FUNCTION ie_title_update(NUMERIC) RETURNS void AS $BODY$
BEGIN
   IF (EXISTS (SELECT id FROM IE_TITLE_VIEW WHERE id = $1)) THEN
      IF (EXISTS (SELECT id FROM ie_title WHERE id = $1)) THEN
         UPDATE ie_title SET title = (SELECT title FROM ie_title_view WHERE id = $1) WHERE id = $1;
      ELSE
         INSERT INTO ie_title (SELECT * FROM IE_TITLE_VIEW WHERE id = $1);
      END IF;
   ELSE
      DELETE FROM ie_title WHERE id = $1;
   END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ie_title_update_trigger_function() RETURNS TRIGGER AS $BODY$
   BEGIN
      IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
         EXECUTE ie_title_update(NEW.intelectualentityid);
      ELSIF (TG_OP = 'DELETE') THEN
         EXECUTE ie_title_update(OLD.intelectualentityid);
      END IF;
      RETURN NULL;
   END;
$BODY$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS ie_title_update_trigger ON ieidentifier;
CREATE TRIGGER ie_title_update_trigger AFTER INSERT OR UPDATE OR DELETE ON ieidentifier FOR EACH ROW EXECUTE PROCEDURE ie_title_update_trigger_function();

CREATE INDEX ie_title_fulltext_idx ON ie_title USING gin(to_tsvector('simple', lower(title)));
